<?php
/**
 * 报表管理类
 * 处理统计报表相关的操作
 */

class ReportManager {
    private $db;
    
    public function __construct($database) {
        $this->db = $database;
    }
    
    /**
     * 获取月度考勤统计
     */
    public function getMonthlyAttendanceReport($year, $month) {
        $startDate = "{$year}-{$month}-01";
        $endDate = date('Y-m-t', strtotime($startDate));
        
        $sql = "SELECT 
                u.id as user_id,
                u.username,
                u.full_name,
                u.department,
                u.position,
                COUNT(ar.id) as total_days,
                SUM(CASE WHEN ar.status = 'normal' THEN 1 ELSE 0 END) as normal_days,
                SUM(CASE WHEN ar.status = 'late' THEN 1 ELSE 0 END) as late_days,
                SUM(CASE WHEN ar.status = 'early_leave' THEN 1 ELSE 0 END) as early_leave_days,
                SUM(CASE WHEN ar.status = 'absent' THEN 1 ELSE 0 END) as absent_days,
                SUM(CASE WHEN ar.status = 'leave' THEN 1 ELSE 0 END) as leave_days,
                SUM(CASE WHEN ar.status = 'business_trip' THEN 1 ELSE 0 END) as business_trip_days,
                SUM(ar.total_work_hours) as total_work_hours,
                AVG(ar.total_work_hours) as avg_work_hours
                FROM users u
                LEFT JOIN attendance_records ar ON u.id = ar.user_id 
                    AND ar.record_date BETWEEN ? AND ?
                WHERE u.status = 1
                GROUP BY u.id, u.username, u.full_name, u.department, u.position
                ORDER BY u.department, u.full_name";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$startDate, $endDate]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * 获取部门考勤统计
     */
    public function getDepartmentAttendanceReport($startDate, $endDate) {
        $sql = "SELECT 
                u.department,
                COUNT(ar.id) as total_records,
                SUM(CASE WHEN ar.status = 'normal' THEN 1 ELSE 0 END) as normal_count,
                SUM(CASE WHEN ar.status = 'late' THEN 1 ELSE 0 END) as late_count,
                SUM(CASE WHEN ar.status = 'early_leave' THEN 1 ELSE 0 END) as early_leave_count,
                SUM(CASE WHEN ar.status = 'absent' THEN 1 ELSE 0 END) as absent_count,
                SUM(CASE WHEN ar.status = 'leave' THEN 1 ELSE 0 END) as leave_count,
                SUM(CASE WHEN ar.status = 'business_trip' THEN 1 ELSE 0 END) as business_trip_count,
                SUM(ar.total_work_hours) as total_work_hours,
                AVG(ar.total_work_hours) as avg_work_hours
                FROM attendance_records ar
                LEFT JOIN users u ON ar.user_id = u.id
                WHERE ar.record_date BETWEEN ? AND ?
                GROUP BY u.department
                ORDER BY u.department";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$startDate, $endDate]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * 获取考勤趋势统计
     */
    public function getAttendanceTrend($startDate, $endDate) {
        $sql = "SELECT 
                DATE(record_date) as date,
                COUNT(*) as total_records,
                SUM(CASE WHEN status = 'normal' THEN 1 ELSE 0 END) as normal_count,
                SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) as late_count,
                SUM(CASE WHEN status = 'early_leave' THEN 1 ELSE 0 END) as early_leave_count,
                SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent_count,
                AVG(total_work_hours) as avg_work_hours
                FROM attendance_records
                WHERE record_date BETWEEN ? AND ?
                GROUP BY DATE(record_date)
                ORDER BY date";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$startDate, $endDate]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * 获取考勤异常统计
     */
    public function getAttendanceExceptionReport($startDate, $endDate) {
        $sql = "SELECT 
                u.full_name,
                u.department,
                u.position,
                ar.record_date,
                ar.morning_clock_in,
                ar.morning_clock_out,
                ar.afternoon_clock_in,
                ar.afternoon_clock_out,
                ar.total_work_hours,
                ar.status,
                ar.notes
                FROM attendance_records ar
                LEFT JOIN users u ON ar.user_id = u.id
                WHERE ar.record_date BETWEEN ? AND ?
                AND ar.status IN ('late', 'early_leave', 'absent')
                ORDER BY ar.record_date DESC, u.department, u.full_name";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$startDate, $endDate]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * 获取加班统计
     */
    public function getOvertimeReport($startDate, $endDate) {
        $sql = "SELECT 
                u.full_name,
                u.department,
                u.position,
                ar.record_date,
                ar.total_work_hours,
                CASE 
                    WHEN ar.total_work_hours > 8 THEN ar.total_work_hours - 8
                    ELSE 0
                END as overtime_hours
                FROM attendance_records ar
                LEFT JOIN users u ON ar.user_id = u.id
                WHERE ar.record_date BETWEEN ? AND ?
                AND ar.total_work_hours > 8
                ORDER BY overtime_hours DESC, ar.record_date DESC";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$startDate, $endDate]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * 获取考勤汇总统计
     */
    public function getAttendanceSummary($startDate, $endDate) {
        $sql = "SELECT 
                COUNT(DISTINCT user_id) as total_users,
                COUNT(*) as total_records,
                SUM(CASE WHEN status = 'normal' THEN 1 ELSE 0 END) as normal_count,
                SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) as late_count,
                SUM(CASE WHEN status = 'early_leave' THEN 1 ELSE 0 END) as early_leave_count,
                SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent_count,
                SUM(CASE WHEN status = 'leave' THEN 1 ELSE 0 END) as leave_count,
                SUM(CASE WHEN status = 'business_trip' THEN 1 ELSE 0 END) as business_trip_count,
                SUM(total_work_hours) as total_work_hours,
                AVG(total_work_hours) as avg_work_hours,
                MAX(total_work_hours) as max_work_hours,
                MIN(total_work_hours) as min_work_hours
                FROM attendance_records
                WHERE record_date BETWEEN ? AND ?";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$startDate, $endDate]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * 获取可用的年份和月份
     */
    public function getAvailableMonths() {
        $sql = "SELECT DISTINCT 
                YEAR(record_date) as year,
                MONTH(record_date) as month,
                DATE_FORMAT(record_date, '%Y-%m') as year_month
                FROM attendance_records
                ORDER BY year DESC, month DESC";
        
        try {
            $stmt = $this->db->query($sql);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            // 如果查询失败，返回空数组
            error_log("获取可用月份失败: " . $e->getMessage());
            return [];
        }
    }
}
?>
